package com.cx.common.util;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.List;
import java.util.Map;
import java.util.Properties;

/**
 * @author flysand
 * @package com.cx.common.util
 * @date 2021-11-07 23:36
 * @des JdbcUtils: jdbc连接
 */
public class JdbcUtils {

    private final static Logger logger = LoggerFactory.getLogger(JdbcUtils.class);

    private static String driverName;
    private static String url;
    private static String username;
    private static String password;

    private static Connection connection = null;

    private static PreparedStatement statement = null;

    private static ResultSet rs = null;


    static {
        // 读取配置
        Properties properties = new Properties();
        InputStream is = JedisUtils.class.getClassLoader().getResourceAsStream("config/mysql.properties");
        try {
            properties.load(is);
            driverName = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }


    }

    public static Connection getConnection() {
        logger.info("换行");

        try {
            Class.forName(driverName);
            connection = DriverManager.getConnection(url, username, password);
            logger.debug("connected mysql database");
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static List<Map<String, Object>> executeSql(String sql) {
        List<Map<String, Object>> dataList = Lists.newArrayList();
        if (null == connection) {
            connection = getConnection();
        }
        try {
            statement = connection.prepareStatement(sql);
            rs = statement.executeQuery();
            dataList = getData(rs);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (null != statement) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }


        }
        return dataList;
    }

    private static List<Map<String, Object>> getData(ResultSet rs) {
        List<Map<String, Object>> dataList = Lists.newArrayList();
        try {
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();

            while (rs.next()) {

                Map<String, Object> columnMap = Maps.newHashMap();
                for (int i = 0; i < columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i + 1);
                    Object val = rs.getObject(columnName);
                    columnMap.put(columnName, val);
                }
                dataList.add(columnMap);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return dataList;
    }

}
